﻿/*****************************************
 * 
 *  copyright (c) by ylshen
 * 
 *  author : ylshen (syl.lucky@gmail.com)
 *  
 *  created at : 08-06-04
 * 
 **************************************** */

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JYStockAnalyser.Model;

namespace JYStockAnalyser.DA
{
    public class StockMapper : ORMapper
    {
        public Stock FindStockByNO(string no)
        {
            string filter = String.Format(" WHERE [no] = '{0}'", no);
            return m_findStockByFilter(filter);
        }

        public List<Stock> FindAllStocks()
        {
            string sql = "SELECT kind, [no], [name], province, trade, location FROM t_stocks ORDER BY [no]";
            List<Stock> stocks = new List<Stock>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    Stock stock = new Stock();
                    stock.Kind = GetReaderInt32Value(reader, 0);
                    stock.NO= GetReaderString(reader, 1);
                    stock.Name = GetReaderString(reader, 2);
                    stock.Province = GetReaderString(reader, 3);
                    stock.Trade = GetReaderString(reader, 4);
                    stock.Location = GetReaderString(reader, 5);
                    stocks.Add(stock);
                }
            }
            return stocks;
        }

        public List<string> FindAllStockNOAndNames()
        {
            string sql = "SELECT [no], [name], [kind] FROM t_stocks ORDER BY [no]";
            List<string> nos = new List<string>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    int kind = GetReaderInt32Value(reader, 2);
                    string no = GetReaderString(reader, 0) + "  " + GetReaderString(reader, 1);
                    if (kind == 1)//若是金融类股票，则在其名称后加上"(金融)"
                    {
                        no += " (金融)";
                    }
                    nos.Add(no);
                }
            }
            return nos;
        }

        public List<string> FindAllTrades()
        {
            string sql = "SELECT distinct trade FROM t_stocks ORDER BY trade";
            List<string> trades = new List<string>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    trades.Add(GetReaderString(reader, 0));
                }
            }
            return trades;
        }

        #region private methods

        private Stock m_findStockByFilter(string filter)
        {
            List<Stock> stocks = m_findStocksByFilter(filter);
            return stocks.Count == 0 ? null : stocks[0];
        }

        private List<Stock> m_findStocksByFilter(string filter)
        {
            string sql = "SELECT [no], [name], companyName, province, trade, address, location, kind FROM t_stocks" + filter;
            List<Stock> stocks = new List<Stock>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    Stock stock = new Stock();
                    stock.NO = GetReaderString(reader, 0);
                    stock.Name = GetReaderString(reader, 1);          
                    stock.CompanyName = GetReaderString(reader, 2);
                    stock.Province = GetReaderString(reader, 3);
                    stock.Trade = GetReaderString(reader, 4);
                    stock.address = GetReaderString(reader, 5);
                    stock.Location = GetReaderString(reader, 6);
                    stock.Kind = GetReaderInt32Value(reader, 7);
                    stocks.Add(stock);
                }
            }
            return stocks;
        }

        #endregion
    }
}
